﻿using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using Modules.Entitys;
using PetaPoco;
using PmSoft;
using PmSoft.ApplicationContext;
using PmSoft.DBContext;
using PmSoft.Repositories;
using PmSoft.Utilities;

namespace Modules.Notify.Repositories
{
    [UseDI(ServiceLifetime.Scoped)]
    public class WorkerNotifyRepository : Repository<WorkerNotify, DefaultDbContext>
    {
        public WorkerNotifyRepository(DefaultDbContext dbContext, IApplicationContext applicationContext, ILogger<WorkerNotifyRepository> logger)
           : base(dbContext, applicationContext)
        {
        }

        /// <summary>
        /// 分页获取工人通知
        /// </summary>
        /// <param name="query"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public IPagedList<WorkerNotify> GetWorkerNotifysByPage(NotifyQuery query, int pageIndex, int pageSize)
        {
            Sql sql = Sql.Builder;
            sql.Select("id")
                .From("worker_notify");
            if (!string.IsNullOrEmpty(query.Keywords))
                sql.Where("(content Like @0 or title Like @0)", "%" + StringUtility.StripSQLInjection(query.Keywords) + "%");
            if (query.Month.HasValue)
                sql.Where("year(create_time)=@0 and month(create_time)=@1", query.Month.Value.Year, query.Month.Value.Month);
            if (query.ProjectId.HasValue)
                sql.Where("project_id = @0", query.ProjectId);
            sql.OrderBy("create_time DESC");
            return GetPagingEntities(pageSize, pageIndex, sql);
        }

        /// <summary>
        /// 根据查询条件获取所有工人通知
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public IEnumerable<WorkerNotify> GetAllWorkerNotifysByQuery(NotifyQuery query)
        {
            Sql sql = Sql.Builder;
            sql.Select("id")
                .From("worker_notify");
            if (!string.IsNullOrEmpty(query.Keywords))
                sql.Where("(content Like @0 or title Like @0)", "%" + StringUtility.StripSQLInjection(query.Keywords) + "%");
            if (query.Month.HasValue)
                sql.Where("year(create_time)='@0' and month(create_time)='@1'", query.Month.Value.Year, query.Month.Value.Month < 10 ? "0" + query.Month.Value.Month : query.Month.Value.Month);
            sql.OrderBy("create_time DESC");
            return PopulateEntitiesByEntityIds(DbContext.Fetch<string>(sql));
        }

        /// <summary>
        /// 获取工人通知总数
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public int GetWorkerNotifyCount(NotifyQuery query)
        {
            Sql sql = Sql.Builder;
            sql.Select("COUNT(DISTINCT id)")
                .From($"worker_notify");
            if (!string.IsNullOrEmpty(query.Keywords))
                sql.Where("(content Like @0 or title Like @0)", "%" + StringUtility.StripSQLInjection(query.Keywords) + "%");
            if (query.Month.HasValue)
                sql.Where("year(create_time)='@0' and month(create_time)='@1'", query.Month.Value.Year, query.Month.Value.Month < 10 ? "0" + query.Month.Value.Month : query.Month.Value.Month);
            return DbContext.ExecuteScalar<int>(sql);
        }

        /// <summary>
        /// 通过人员Id获取该人员所属项目的工人通知
        /// </summary>
        /// <param name="personId"></param>
        /// <returns></returns>
        public IEnumerable<WorkerNotify> GetAllWorkerNotifiesByPersonId(int personId)
        {
            Sql sql = Sql.Builder;
            sql.Select("id")
                .From("worker_notify")
                .Where("project_id in (select project_id from project_group_member where person_id=@0)", personId);
            return PopulateEntitiesByEntityIds(DbContext.Fetch<string>(sql));
        }
    }
}